# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
1.获取中移动项目数据库的信息生成相应的对账文件
2.获取数据库信息并对信息进行组合
3.更新数据库字段信息
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

from decimal import Decimal
import os
from msydwork.config.config_msydonline import cursor,db
from pycacho.cachobase.file_deal import get_line
from pycacho.cachobase.file_deal import an_save_txt
from pycacho.cachobase.deal_time import minus_date

org_id = '888005000000002'
org_nm = '民盛互联网小额贷款'
pick_dt = minus_date(1,'s')


def truncate_table():
    print("开始进行数据库数据初始化-清除数据")
    data = get_line('F:\PythonProject\msydwork\\test\ydonlinedb-clear.sql','TRUNCATE','TRUNCATE')
    for sql in data:
        print(sql)
        cursor.execute(sql)
        db.commit()

def delete_table():
    print("开始进行数据库数据初始化-清除数据")
    data = get_line('F:\PythonProject\msydwork\\test\ydonlinedb-clear.sql','delete','delete')
    for sql in data:
        print(sql)
        cursor.execute(sql)
        db.commit()

def drop_table():
    print("开始进行数据库数据初始化-删除自动任务数据库表")
    data = get_line('F:\PythonProject\msydwork\\test\cronjob-drop.sql', 'DROP','DROP')
    for sql in data:
        try:
            cursor.execute(sql)
            db.commit()
            print(sql)
        except:
            print("自动任务数据库表已经被删除......")

# 中移动项目：对账文件
def get_payInfo(m,n):
    sql = 'select "P",brw_ord_no,brw_ord_dt,process_no,credit_date,credit_amount,"0","%s" from online_main where credit_status=1 and loan_status in (0,1,7) and SUBSTR(brw_ord_no,1,10) ="msydonline" and id>="%s" and id<="%s"' \
          'UNION select "R",brw_ord_no,brw_ord_dt,process_no,credit_date,"0",credit_amount,"%s" from online_main where credit_status=1 and loan_status in (7) and SUBSTR(brw_ord_no,1,10) ="msydonline" and id>="%s" and id<="%s";' %(pick_dt,m,n,pick_dt,m,n)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        row = []
        for data in result:
            try:
                data = data.strftime('%Y%m%d%H%M')[0:8]
            except:
                data =str(data)
            row.append(data)
        info.append(row)
    #print(info)
    return(info)


# 批量扣款回盘文件
def get_repayInfo(m,n):
    sql = 'select r.brw_ord_no,l.brw_ord_dt,l.org_order_no,l.org_order_dt,r.rpy_seq,r.repay_amt,r.repay_amt,r.brw_ord_no,r.start_time,"S","还款成功","ABC","0003","001" from ms_repay_order r,pkg_suc_record l where r.brw_ord_no=l.brw_ord_no and SUBSTR(r.brw_ord_no,1,10) ="msydonline" and r.status="N" and r.id>="%s" and r.id<="%s";' %(m,n)
    cursor.execute(sql)
    results = cursor.fetchall()
    info = []
    for result in results:
        row = []
        for data in result:
            try:
                data = data.strftime('%Y%m%d%H%M')[0:8]
            except:
                data =str(data)
            row.append(data)
        info.append(row)
    #print(info)
    return(info)


# 根据外部借款订单号获取内部生成的案件号
def get_org(brwOrdNo):
    cursor.execute('select process_no from online_main where brw_ord_no = "%s";' %(brwOrdNo))
    try:
        orgOrderNo = cursor.fetchone()  # 获取一条数据
        db.commit()
       # print (orgOrderNo[0])
        return(orgOrderNo[0])
    except:
        print('查无数据！')

# 根据外部借款订单号获取内部生成的案件号
def sign():
    cursor.execute('select brw_ord_no from sign_record where sign_result = "F";')
    try:
        brw_ord_no = cursor.fetchall()  # 获取一条数据
        #db.commit()
        print(brw_ord_no)
        return (brw_ord_no)
    except:
        print('查无数据！')

# 根据外部借款订单号获取内部生成的案件号
def white_user(id_card,remark):
    sql = "INSERT INTO white_list (type, value, available, remark, c_time, u_time) VALUES (1, '%s', 1, '%s', now(), now());" % (id_card,remark)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        print(remark + '白名单信息插入到pro_main成功！')

def update_msydloan(b_date):
    sql = "update ms_system_date set business_date=%s;"
    cursor.execute(sql, [b_date])
    db.commit()

# 获取对账文件信息
def get_dat(file):
    with open(file, 'r',encoding= 'utf-8') as fp:
        for line in fp.readlines():
            line = line.lstrip().replace(' ','').replace('\n','')
            data = line.split('|')
            print(data)


def get_file(file_type,date):
    if file_type == '01':
        file = 'F:\\' + org_id + '_CAPITAL_TRANS_ORDER_' + date + '_001.txt'
        info = get_payInfo('1', '1000')                          # file_type = '01'  业务办理成功/退货明细文件
    else:
        file = 'F:\\' + 'BACK_' + org_id + '_BATCH_DETAILS_'+ date + '_001.txt'     # 批量扣款回盘文件
        info = get_repayInfo(1,1000)
    if (os.path.exists(file)):
        os.remove(file)                 # 删除文件
        print('本地已存在文件：%s，先删除！' %(file))
    TotCnt = len(info)
    TotAmt = Decimal('0')
    for pay in info:
        # file_type01对应5，file_type02对应6, file_type04对应4
        if file_type == '01':
            TotAmt = TotAmt + Decimal(pay[5])
        else:
            TotAmt = TotAmt + Decimal(pay[5])
    if file_type == '02':
        total = str(TotCnt) + '|' + '%.2f' % TotAmt + '|' + str(TotCnt) + '|' + '%.2f' % TotAmt + '|0|0.00|'
    else:
        total = str(TotCnt) + '|' + '%.2f' % TotAmt + '|'  # 浮点型两位小数点的字符串
    print(total)
    an_save_txt(file, total)          # 写入文件头

    for pay in info:
        order = '|'.join(pay)
        detail = order + '|'
        print(detail)
        an_save_txt(file, detail)     # 写入文件详情

    an_save_txt(file, '@END@')        # 写入文件尾
    return file


if __name__ == '__main__':
    #qbmsloan()
    #get_payInfo('13','18')
    #get_file('01','20200702')
    # batch_main('01',[5,18,23],'20200312')
    #get_repayInfo(1, 100)
    #sign()
    get_org('msydonline_202009240000002')